#!/bin/bash

ExportDir="/tmp/airshow/"
Data=$(date +%Y%m%d%H%M%S)
File_name="Report_${Data}.csv"
Login_count="Login_report_${Data}.csv"
#Mail_list=("jc.wu@windfindtech.com" "vincent.meng@windfindtech.com")
#Mail_list=("jc.wu@windfindtech.com" "vincent.meng@windfindtech.com" "lanht@chinatelecom.cn" "3488278577@qq.com")
#Mail_list=("3488278577@qq.com")
#Mail_list=("lanht@chinatelecom.cn" "3488278577@qq.com")
GbkFile="ReportGbk_${Data}.csv"
LoginGBKFile="Login_reportGBK_${Data}.csv"

# touch "${ExportDir}${File_name}"
# echo "我是严肃的测试文档,非常严肃" > ${ExportDir}${File_name}

mysql -h192.168.20.188 -P3306 -uroot -p1zhuhai-qwer iZH_Analysis -e " update iZH_Analysis.RadiusAccountHZ set calledstationid = substring(calledstationid,1,17); truncate table iZH_Analysis.FactRadiusHZTemp;insert into FactRadiusHZTemp select * from iZH_Analysis.RadiusAccountHZ where calledstationid in (select mac from iZH_Analysis.RM_Ap WHERE locationID IN (select ID from iZH_Analysis.RM_Location where name like '%十字门%')) AND starttime like CONCAT((select substring(DATE_SUB(NOW(),INTERVAL 9 HOUR), 1, 13)),'%'); select loc.name as name ,loc.mac as mac ,loc.installPosition as installPosition,ifnull(round(sum(inputoctets) / 1024 / 1024, 2), 0) as inputoctets,ifnull(round(sum(outputoctets) / 1024 / 1024, 2), 0) as outputoctets,ifnull(round(sum(inputoctets + outputoctets) / 1024 / 1024, 2), 0) as total from (SELECT l.name,a.mac,a.installPosition from iZH_Analysis.RM_Location l Left join iZH_Analysis.RM_Ap a on l.id = a.locationID WHERE a.locationID IN(select ID from iZH_Analysis.RM_Location where name like '%十字门%') ) as loc left join iZH_Analysis.FactRadiusHZTemp r on loc.mac = r.calledstationid group by loc.name,mac,calledstationid" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > "${ExportDir}${File_name}"

mysql -h192.168.20.188 -P3306 -uroot -p1zhuhai-qwer iZH_Analysis -e "select re.name as '场点',count(username) as '使用人数' from (select loc.name as name,username from (SELECT l.name,a.mac from iZH_Analysis.RM_Location l Left join iZH_Analysis.RM_Ap a on l.id = a.locationID WHERE a.locationID IN(select ID from iZH_Analysis.RM_Location where name like '%十字门%') ) as loc left join iZH_Analysis.FactRadiusHZTemp r on loc.mac = r.calledstationid where username is not null group by loc.name,username) as re group by name;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > "${ExportDir}${Login_count}"

echo " " >> ${ExportDir}${File_name}
echo " " >> ${ExportDir}${Login_count}


iconv -f UTF-8 -t GBK ${ExportDir}${File_name} -o ${ExportDir}${GbkFile}
iconv -f UTF-8 -t GBK ${ExportDir}${Login_count} -o ${ExportDir}${LoginGBKFile}

echo -e "${Date}特定场点使用报告,详情请查看附件" | mutt -s "前一小时特定wifi使用报告" ${Mail_list[*]} -a "${ExportDir}${GbkFile}" "${ExportDir}${LoginGBKFile}"



